using System;
using System.Collections.Generic;
using System.Text;

using student_factory.Metier;

using System.Data;
using System.Data.SqlClient;

namespace student_factory.DAO
{
    class ProfilDAO
    {

        private static String connection = "data source=localhost;initial catalog=STUDENTS_FACTORY;User ID=sa;Password=";

        public static IList<Profil> getProfils()
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            IList<Profil> profils = new List<Profil>();

            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                commande.CommandText = "SELECT * FROM PROFILS";

                reader = commande.ExecuteReader();

                while (reader.Read())
                {
                    Profil profil = new Profil();
                    profil.Id_i = reader["ID_I"].ToString();
                    profil.Pro_nom = reader["PRO_NOM"].ToString();
                    profils.Add(profil);
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return profils;
        }

        public static IList<Profil> getNotFilledProfilByProjet(string idProjet)
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            IList<Profil> profils = new List<Profil>();

            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                commande.CommandText = "SELECT c.ID_I, PRO_NOM FROM CONTENU_PROJET as c, ETUDIANTS as e, PROFILS as p WHERE c.ID_P = '" + idProjet + "' AND c.ID_I = p.ID_I GROUP BY c.ID_I, PRO_NOM HAVING COUNT(ETU_NOM) < SUM(CPR_QUANTITE)";

                reader = commande.ExecuteReader();

                while (reader.Read())
                {
                    Profil profil = new Profil();
                    profil.Id_i = reader["ID_I"].ToString();
                    profil.Pro_nom = reader["PRO_NOM"].ToString();
                    profils.Add(profil);
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return profils;
        }

        public static IList<Profil> getProfilesFromProject(Projet p)
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            IList<Profil> profiles = null;
            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();

                commande.CommandText = "SELECT PROFILS.ID_I as ID_I,PROFILS.PRO_NOM AS PRO_NOM,PROFILS.PRO_DESCRIPTION AS PRO_DESCRIPTION from PROFILS ,CONTENU_PROJET WHERE PROFILS.ID_I like CONTENU_PROJET.ID_I AND CONTENU_PROJET.ID_P LIKE '" + p.Id_p + "'";

                reader = commande.ExecuteReader();



                while (reader.Read())
                {
                    string id = reader["ID_I"].ToString();
                    string desc = reader["PRO_DESCRIPTION"].ToString();
                    string nom = reader["PRO_NOM"].ToString();

                    profiles.Add(new Profil(id,desc,nom) );
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return profiles;
        }

        public static int getNbProfilsFromProject(Projet p) 
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            int nbProfiles = -1;
            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();

                commande.CommandText = "SELECT CPR_QUANTITE FROM CONTENU_PROJET WHERE ID_P like '" + p.Id_p + "'";

                reader = commande.ExecuteReader();

                while (reader.Read())
                {
                    nbProfiles = Int32.Parse(reader["CPR_QUANTITE"].ToString());
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return nbProfiles;
        }

        public static Profil getProfilFromEtudiant( Etudiant etu )
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            Profil p = null;
            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();

                commande.CommandText = "SELECT * FROM PROFILS WHERE ID_I like '" + etu.Id_i + "'";

                reader = commande.ExecuteReader();

                while (reader.Read())
                {
                    string id_i = reader[ "ID_I" ].ToString();
                    string desc = reader[ "PRO_DESCRIPTION" ].ToString();
                    string nom = reader[ "PRO_NOM" ].ToString();
                    p = new Profil(id_i, desc, nom);
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return p;
        }

    }
}
